import psycopg2
from datetime import datetime

DB_CONFIG = {
    "dbname": "game_trading_v2",
    "user": "postgres",
    "password": "145114",
    "host": "localhost",
    "port": "5432"
}

def get_conn():
    return psycopg2.connect(**DB_CONFIG)

def get_status_values():
    """
    获取数据库中实际使用的状态值，用于调试
    """
    try:
        with get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT DISTINCT Status FROM Equipments")
                return [row[0] for row in cur.fetchall()]
    except Exception as e:
        print(f"获取状态值错误: {e}")
        return []

def get_cart_items(user_id):
    """
    获取用户购物车中的所有装备信息
    返回 [(装备ID, 装备名称, 价格, 卖家ID, 上架时间, 状态), ...]
    """
    try:
        sql = """
            SELECT e.EquipmentID, e.EquipmentName, e.Price, e.SellerID, e.ListedDate, e.Status
            FROM Cart c
            JOIN Equipments e ON c.EquipmentID = e.EquipmentID
            WHERE c.UserID = %s
            ORDER BY e.ListedDate DESC
        """
        with get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute(sql, (user_id,))
                return cur.fetchall()
    except Exception as e:
        print(f"获取购物车错误: {e}")
        return []  # 返回空列表代替崩溃

def add_to_cart(user_id, equipment_id):
    """
    将装备加入购物车
    """
    try:
        with get_conn() as conn:
            with conn.cursor() as cur:
                # 检查装备是否存在且为在售状态
                cur.execute("SELECT Status FROM Equipments WHERE EquipmentID=%s", (equipment_id,))
                row = cur.fetchone()
                if not row:
                    return False, "装备不存在"
                if row[0] != "active":  # 修改: 使用英文状态值"active"代替"在售"
                    return False, "装备已下架或已售出"
                    
                # 检查是否已在购物车
                cur.execute("SELECT 1 FROM Cart WHERE UserID=%s AND EquipmentID=%s", (user_id, equipment_id))
                if cur.fetchone():
                    return False, "该装备已在购物车中"
                    
                # 不能购买自己发布的装备
                cur.execute("SELECT SellerID FROM Equipments WHERE EquipmentID=%s", (equipment_id,))
                seller_id = cur.fetchone()[0]
                if str(seller_id) == str(user_id):
                    return False, "不能购买自己发布的装备"
                    
                # 插入购物车
                cur.execute(
                    "INSERT INTO Cart (UserID, EquipmentID, AddedDate) VALUES (%s, %s, %s)",
                    (user_id, equipment_id, datetime.now())
                )
                conn.commit()
                return True, "成功添加到购物车"
    except Exception as e:
        print(f"添加购物车错误: {e}")
        return False, str(e)

def remove_from_cart(user_id, equipment_id):
    """
    从购物车移除装备
    """
    try:
        with get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "DELETE FROM Cart WHERE UserID=%s AND EquipmentID=%s",
                    (user_id, equipment_id)
                )
                conn.commit()
                return True, "已从购物车移除"
    except Exception as e:
        print(f"移除购物车错误: {e}")
        return False, str(e)

def checkout_selected_items(user_id, equipment_ids):
    """
    结算选中的购物车物品 - 使用checkout_cart存储过程
    """
    if not equipment_ids:
        return False, "未选择任何装备"
    
    # 确保equipment_ids是整数列表
    try:
        if isinstance(equipment_ids, str):
            equipment_ids = [int(equipment_ids)]
        else:
            equipment_ids = [int(id) for id in equipment_ids]
    except ValueError:
        return False, "装备ID格式错误"
    
    try:
        conn = get_conn()
        cur = conn.cursor()
        
        try:
            # 创建临时表存储结果
            cur.execute("""
                CREATE TEMP TABLE checkout_result (
                    success BOOLEAN,
                    message TEXT
                )
            """)
            
            # 正确方式调用存储过程并获取OUT参数
            cur.execute("""
                DO $$
                DECLARE
                    success_out BOOLEAN;
                    message_out TEXT;
                BEGIN
                    CALL checkout_cart(%s, %s, success_out, message_out);
                    INSERT INTO checkout_result VALUES (success_out, message_out);
                END $$;
            """, (user_id, equipment_ids))
            
            # 获取存储过程的结果
            cur.execute("SELECT * FROM checkout_result")
            result = cur.fetchone()
            
            if result:
                success = result[0]  # 第一列是success
                message = result[1]  # 第二列是message
                
                conn.commit()
                return success, message
            else:
                conn.rollback()
                return False, "结算处理失败，未返回结果"
                
        except Exception as e:
            conn.rollback()
            print(f"结算调用存储过程错误: {e}")
            return False, f"结算失败: {e}"
        finally:
            # 清理临时表
            try:
                cur.execute("DROP TABLE IF EXISTS checkout_result")
            except:
                pass
            cur.close()
            conn.close()
            
    except Exception as e:
        print(f"结算数据库连接错误: {e}")
        return False, f"数据库连接错误: {e}"